# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html
import pymysql
from dbutils.pooled_db import PooledDB
# useful for handling different item types with a single interface
from itemadapter import ItemAdapter

db_dict = {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "root",
    "passwd": "123456",
    "db": "blade2",
    "maxcached": 200,  # 最大空闲数
    "charset": "utf8",
    "setsession": ['SET AUTOCOMMIT = 1'],
    "connect_timeout": 60,
    "read_timeout": 60,
    "write_timeout": 60,
}

class RecruitPipeline:
    def __init__(self):
        self.mysql_pool_list = PooledDB(pymysql, **db_dict)
        self.conn = self.mysql_pool_list.connection()
        # 使用 cursor() 方法创建一个游标对象 cursor
        self.cursor = self.conn.cursor()

    def close_spider(self, spider):
        print('----------关闭数据库资源-----------')
        # 关闭游标
        self.cursor.close()
        # 关闭连接
        self.conn.close()
    def process_item(self, item, spider):
        print('type', type(item))
        insert_sql = """
                    insert into recruit(job_name,company,type,salary,region,education,experience,jd,jr,tag,source) value(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
                    """
        if 'type' not in item:
            item['type'] = ''
        if 'region' not in item:
            item['region'] = ''
        if 'jd' not in item:
            item['jd'] = ''
        if 'jr' not in item:
            item['jr'] = ''
        if 'tag' not in item:
            item['tag'] = ''
        self.cursor.execute(insert_sql, (
            item['job_name'], item['company'], item['type'], item['salary'],
            item['region'], item['education'],item['experience'],item['jd'], item['jr'], item['tag'], item['source']))
        self.conn.commit()
        return item
